
package com.tjing.bussiness.controller.group;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.tjing.frame.services.DbServices;
import com.tjing.frame.util.CodeHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;

/**
 * Created by cwp on 2016/12/16.
 */
@RestController
@RequestMapping("/public/group")
public class JtController {
    static Logger logger = Logger.getLogger(JtController.class);
    @Autowired
    private DbServices dbServices;
    @SuppressWarnings("unused")
    //门票销售统计
    @RequestMapping("/queryTicketSaleList")
    public List<Object[]> queryTicketSaleList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y-%m-%d')>=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("createTime"));
        }
        if (jobj.containsKey("createTime2")) {
            where += " and date_format(ts.create_time,'%Y-%m-%d')<=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("createTime2"));
        }
        if (jobj.containsKey("playDate")) {
            where += " and date_format(ts.play_date,'%Y-%m-%d')>=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("playDate"));
        }
        if (jobj.containsKey("playDate2")) {
            where += " and date_format(ts.play_date,'%Y-%m-%d')<=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("playDate2"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y-%m-%d') as createTime,"//售票日期
                + "date_format(ts.play_date,'%Y-%m-%d') as playDate,"//演出日期
                + "cash,"//现金
                + "member_card,"// 充值卡
                + "credit_card,"//信用卡
                + "buy_credit,"// 挂账
                + "ticket_now,"//原价票
                + "ticket_number,"// 有效票
                + "ticket_float,"//浮动票
                + "ticket_black,"// 退票数
                + "ticket_exchange,"// 换座票数
                + "ticket_free,"// 免票数
                + "ticket_min,"// 最低票
                + "ticket_off,"// 优惠票
                + "ticket_change "// 改签票数
                + "from jt_ticket_sale ts"
                + where + " order by ts.theater_id,date_format(ts.create_time,'%Y-%m-%d')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
    //门票销售月统计
    @RequestMapping("/queryTicketSaleMonthList")
    public List<Object[]> queryTicketSaleMonthList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y-%m')=?";
            paramMap.put(i++, jobj.getString("createTime"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y-%m') as createTime,"//售票日期
                + "sum(cash),"//现金
                + "sum(member_card),"// 充值卡
                + "sum(credit_card),"//信用卡
                + "sum(buy_credit),"// 挂账
                + "sum(ticket_now),"//原价票
                + "sum(ticket_number),"// 有效票
                + "sum(ticket_float),"//浮动票
                + "sum(ticket_black),"// 退票数
                + "sum(ticket_exchange),"// 换座票数
                + "sum(ticket_free),"// 免票数
                + "sum(ticket_min),"// 最低票
                + "sum(ticket_off),"// 优惠票
                + "sum(ticket_change) "// 改签票数
                + "from jt_ticket_sale ts"
                + where +" group by ts.theater_id,date_format(ts.create_time,'%Y-%m')"
                + " order by ts.theater_id,date_format(ts.create_time,'%Y-%m')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
    //门票销售年统计
    @RequestMapping("/queryTicketSaleYearList")
    public List<Object[]> queryTicketSaleYearList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y')=?";
            paramMap.put(i++, jobj.getString("createTime"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y') as createTime,"//售票日期
                + "sum(cash),"//现金
                + "sum(member_card),"// 充值卡
                + "sum(credit_card),"//信用卡
                + "sum(buy_credit),"// 挂账
                + "sum(ticket_now),"//原价票
                + "sum(ticket_number),"// 有效票
                + "sum(ticket_float),"//浮动票
                + "sum(ticket_black),"// 退票数
                + "sum(ticket_exchange),"// 换座票数
                + "sum(ticket_free),"// 免票数
                + "sum(ticket_min),"// 最低票
                + "sum(ticket_off),"// 优惠票
                + "sum(ticket_change) "// 改签票数
                + "from jt_ticket_sale ts"
                + where +" group by ts.theater_id,date_format(ts.create_time,'%Y')"
                + " order by ts.theater_id,date_format(ts.create_time,'%Y')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
    //超市销售日统计
    @RequestMapping("/querySuperMarketSaleList")
    public List<Object[]> querySuperMarketSaleList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y-%m-%d')>=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("createTime"));
        }
        if (jobj.containsKey("createTime2")) {
            where += " and date_format(ts.create_time,'%Y-%m-%d')<=date_format(?,'%Y-%m-%d')";
            paramMap.put(i++, jobj.getString("createTime2"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y-%m-%d') as createTime,"//消费日期
                + "name,"
                + "type,"
                + "price,"
                + "count,"
                + "total"
                + " from jt_supermarket_sale ts"
                + where + " order by ts.theater_id,date_format(ts.create_time,'%Y-%m-%d')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
    //超市销售月统计
    @RequestMapping("/querySuperMarketSaleMonthList")
    public List<Object[]> querySuperMarketSaleMonthList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y-%m')=?";
            paramMap.put(i++, jobj.getString("createTime"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y-%m') as createTime,"//消费日期
                + "name,"
                + "type,"
                + "price,"
                + "sum(count),"
                + "sum(total)"
                + " from jt_supermarket_sale ts"
                + where +" group by ts.theater_id,date_format(ts.create_time,'%Y-%m'),name,type,price"
                + " order by ts.theater_id,date_format(ts.create_time,'%Y-%m')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
    //超市销售年统计
    @RequestMapping("/querySuperMarketSaleYearList")
    public List<Object[]> querySuperMarketSaleYearList(String paramString, HttpServletRequest request) {
        JSONObject jobj = JSON.parseObject(paramString);
        HashMap<Integer, Object> paramMap = new HashMap<Integer, Object>();

        String where = " where 1=1";
        int i = 0;
        if (jobj.containsKey("theaterId")) {
            where += " and ts.theater_id=?";
            paramMap.put(i++, jobj.getInteger("theaterId"));
        }
        if (jobj.containsKey("createTime")) {
            where += " and date_format(ts.create_time,'%Y')=?";
            paramMap.put(i++, jobj.getString("createTime"));
        }

        String sql ="select (select name from jt_theater where id=ts.theater_id) as theaterName,"//剧场
                + "date_format(ts.create_time,'%Y') as createTime,"//消费日期
                + "name,"
                + "type,"
                + "price,"
                + "sum(count),"
                + "sum(total)"
                + " from jt_supermarket_sale ts"
                + where +" group by ts.theater_id,date_format(ts.create_time,'%Y'),name,type,price"
                + " order by ts.theater_id,date_format(ts.create_time,'%Y')";
        List<Object[]> result = dbServices.findListBySql(sql, paramMap);

        request.getSession().setAttribute("data", result);
        return result;
    }
}
